const { DB } = require("../config")
const { listObjToTreeObj } = require("../utils")

/**
 *
 * @description 关联所有相关表进行联合查询
 * @param {*} isSingle 是否是查询单个
 * @param {*} options 查询时携带的参数配置
 */
async function getCustomers(isSingle, options) {
	const childSql =
		"SELECT " +
		" cttag.customerId, " +
		" GROUP_CONCAT( tags.`id` SEPARATOR ',' ) AS id," +
		" GROUP_CONCAT( tags.`name` SEPARATOR ',' ) AS name," +
		" GROUP_CONCAT( tags.`type` SEPARATOR ',' ) AS type," +
		" GROUP_CONCAT( tags.`createAt` SEPARATOR ',' ) AS createAt," +
		" GROUP_CONCAT( tags.`updatedAt` SEPARATOR ',' ) AS updatedAt" +
		" FROM `customer_tags` AS cttag INNER JOIN `tags` ON cttag.tagId = tags.id " +
		" GROUP BY cttag.customerId"

	const baseSql =
		"SELECT " +
		" ct.*," +
		// 地址信息
		" GROUP_CONCAT( cta.`id` SEPARATOR ',' ) AS addressId," +
		" GROUP_CONCAT( cta.`address` SEPARATOR ',' ) AS addressAddress," +
		" GROUP_CONCAT( cta.`local` SEPARATOR ',' ) AS addressLocal," +
		" GROUP_CONCAT( cta.`phone` SEPARATOR ',' ) AS addressPhone," +
		" GROUP_CONCAT( cta.`managerId` SEPARATOR ',' ) AS addressManagerId," +
		" GROUP_CONCAT( cta.`createAt` SEPARATOR ',' ) AS addressCreateAt," +
		" GROUP_CONCAT( cta.`updatedAt` SEPARATOR ',' ) AS addressUpdatedAt, " +
		// 标签信息
		" GROUP_CONCAT( ctTags.`id` SEPARATOR ',' ) AS tagId," +
		" GROUP_CONCAT( ctTags.`name` SEPARATOR ',' ) AS tagName," +
		" GROUP_CONCAT( ctTags.`type` SEPARATOR ',' ) AS tagType," +
		" GROUP_CONCAT( ctTags.`createAt` SEPARATOR ',' ) AS tagCreateAt," +
		" GROUP_CONCAT( ctTags.`updatedAt` SEPARATOR ',' ) AS tagUpdateAt," +
		// 农场信息
		" GROUP_CONCAT( farm.`id` SEPARATOR ',' ) AS farmId," +
		" GROUP_CONCAT( farm.`name` SEPARATOR ',' ) AS farmName," +
		" GROUP_CONCAT( farm.`managerId` SEPARATOR ',' ) AS farmManagerId," +
		" GROUP_CONCAT( farm.`latitude` SEPARATOR ',' ) AS farmLatitude," +
		" GROUP_CONCAT( farm.`longitude` SEPARATOR ',' ) AS farmLongitude," +
		" GROUP_CONCAT( farm.`area` SEPARATOR ',' ) AS farmArea," +
		" GROUP_CONCAT( farm.`mainProduce` SEPARATOR ',' ) AS farmMainProduce," +
		" GROUP_CONCAT( farm.`createAt` SEPARATOR ',' ) AS farmCreateAt," +
		" GROUP_CONCAT( farm.`updatedAt` SEPARATOR ',' ) AS farmUpdateAt, " +
		// 订单信息
		" GROUP_CONCAT( orders.`name` SEPARATOR ',' ) AS ordersName, " +
		" GROUP_CONCAT( orders.`ordersId` SEPARATOR ',' ) AS ordersOrdersId, " +
		" GROUP_CONCAT( orders.`managerId` SEPARATOR ',' ) AS ordersManagerId, " +
		" GROUP_CONCAT( orders.`status` SEPARATOR ',' ) AS ordersStatus, " +
		" GROUP_CONCAT( orders.`person` SEPARATOR ',' ) AS ordersPerson " +
		" FROM" +
		"	`customers` AS ct" +
		" LEFT JOIN `customer_address` AS cta ON ct.id = cta.managerId " +
		" LEFT JOIN `customer_farms` AS farm ON ct.id = farm.managerId " +
		" LEFT JOIN ( " +
		childSql +
		" ) AS ctTags ON ct.id = ctTags.customerId" +
		" LEFT JOIN `orders` ON ct.id = orders.managerId " +
		" GROUP BY ct.id"

	let multiple

	try {
		const { id, pageSize = 5, pageNum = 1 } = options
		let args = []

		if (isSingle) {
			multiple = " HAVING ct.id=?"
			args.push(id)
		} else {
			const offset = (pageNum - 1) * pageSize
			multiple = ` LIMIT ${pageSize} OFFSET ${offset} `
		}

		const sql = baseSql + multiple
		// const sql = testSql + multiple

		const [rows] = await DB.execute(sql, args)

		const newList = rows.map((row) => {
			return listObjToTreeObj(row, [
				{ prefix: "tag", id: "tagType" },
				{ prefix: "farm", id: "farmManagerId" },
				{ prefix: "address", id: "addressManagerId" },
				{ prefix: "orders", id: "ordersManagerId" }
			])
		})

		return newList
	} catch (error) {
		console.error("error", error)
		throw error
	}
}

/**
 *
 * @description 简单的单表查询，不连表查询
 * @param {*} isSingle 是否是查询单个
 * @param {*} options 查询时携带的参数配置
 */
async function getSimpleData(isSingle, options) {
	const baseSql = "SELECT * FROM `customers` "
	let multiple

	const { id, pageSize = 5, pageNum = 1 } = options
	const args = []

	try {
		if (isSingle) {
			multiple = " WHERE id=?"
			args.push(id)
		} else {
			const offset = (pageNum - 1) * pageSize
			multiple = ` LIMIT ${pageSize} OFFSET ${offset} `
		}

		const sql = baseSql + multiple

		const [rows] = await DB.execute(sql, args)

		return rows
	} catch (error) {
		console.error("error", error)
		throw error
	}
}

module.exports = {
	getSimpleData,
	getCustomers
}
